﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DBEngine.Elements;
using System.Data.SqlClient;

namespace DBEngine.Tables
{
    abstract class OrdersTable : Table
    {
        protected Dictionary<Enum, SqlDbType> mTableFields;

        protected OrdersTable(string mTableName, Dictionary<Enum, SqlDbType> mTableFields):base(mTableName)
        {
            this.mTableFields = mTableFields;
            this.mTableFields.Add(Order.Fields.ID, SqlDbType.Int);
            this.mTableFields.Add(Order.Fields.ClientID, SqlDbType.Int);
            this.mTableFields.Add(Order.Fields.ExaminationID, SqlDbType.Int);
            this.mTableFields.Add(Order.Fields.OrderDate, SqlDbType.SmallDateTime);
            this.mTableFields.Add(Order.Fields.Price, SqlDbType.Int);

        }
     
        override protected string SetInsertQuery(Element newElement, string cols, string values, List<SqlParameter> paramsList)
        {
            Order order = (Order)newElement;
            string retval = "";
           
            int i = 0;
            string paramName;
            Order.Fields colName;
            SqlParameter param;
            foreach (Order.Fields orderField in Enum.GetValues(typeof(Order.Fields)))
            {
                colName = orderField;
                paramName = @"@" + colName + "Param";

                if (order[colName] == null || colName == Order.Fields.ID)
                    continue;

                if (i++ > 0)
                {
                    cols += ", ";
                    values += ", ";
                }

                switch (colName)
                {
                    case Order.Fields.OrderElement:

                        OrderElement orderElement = (OrderElement)order[colName];
                        int j = 0;
                        foreach (KeyValuePair<Enum, SqlDbType> pair in mTableFields)
                        {
                            Enum elementField = pair.Key;
                            if (elementField.ToString() == Order.Fields.ID.ToString() ||
                                elementField.ToString() == Order.Fields.ClientID.ToString() ||
                                elementField.ToString() == Order.Fields.ExaminationID.ToString() ||
                                elementField.ToString() == Order.Fields.OrderDate.ToString() ||
                                elementField.ToString() == Order.Fields.Price.ToString())
                            {
                                continue;
                            }
                            else
                            {
                                if (j++ > 0)
                                {
                                    cols += ", ";
                                    values += ", ";
                                }
                                paramName = @"@" + elementField + "Param";

                                cols += elementField;
                                values += paramName;

                                param = new SqlParameter(paramName, pair.Value);
                                param.Value = (orderElement[elementField]);
                                paramsList.Add(param);
                            }
                        }
                        break;

                    default:
                        cols += colName;
                        values += paramName;
                        param = new SqlParameter(paramName, mTableFields[colName]);
                        param.Value = order[colName];
                        paramsList.Add(param);
                        break;
                }
            }
            cols += ")";
            values += ")";
            retval += cols + " " + values + @"; Select @@IDENTITY";

            return retval;
        }
        
        override protected string SetUpdateQuery(Element element, List<SqlParameter> paramsList)
        {
            Order order = (Order)element;
            string retval = "";
            string updateString = "";

            int i = 0;
            string paramName;
            Order.Fields colName;
            SqlParameter param;
            
            foreach (Order.Fields orderField in Enum.GetValues(typeof(Order.Fields)))
            {
                colName = orderField;
                paramName = @"@" + colName + "Param";

                if (order[colName] == null || colName == Order.Fields.ID)
                    continue;

                if (i == 0)
                {
                    updateString += "Set ";
                }
                if (i++ > 0)
                {
                    updateString += ", ";
                }

                switch (colName)
                {
                    case Order.Fields.OrderElement:

                        OrderElement orderElement = (OrderElement)order[colName];
                        int j = 0;
                        foreach (KeyValuePair<Enum, SqlDbType> pair in mTableFields)
                        {
                            Enum elementField = pair.Key;
                            if (elementField.ToString() == Order.Fields.ID.ToString() ||
                                elementField.ToString() == Order.Fields.ClientID.ToString() ||
                                elementField.ToString() == Order.Fields.ExaminationID.ToString() ||
                                elementField.ToString() == Order.Fields.OrderDate.ToString() ||
                                elementField.ToString() == Order.Fields.Price.ToString())
                            {
                                continue;
                            }
                            else
                            {
                                if (j++ > 0)
                                {
                                    updateString += ", ";
                                }
                                paramName = @"@" + elementField + "Param";
                                updateString += elementField + " = " + paramName;

                                param = new SqlParameter(paramName, pair.Value);
                                param.Value = orderElement[elementField];
                                paramsList.Add(param);

                            }
                        }
                        break;

                    default:
                        updateString += colName + " = " + paramName;
                        param = new SqlParameter(paramName, mTableFields[colName]);
                        param.Value = order[colName];
                        paramsList.Add(param);
                        break;
                }
            }

            retval = updateString ;
            return retval;
        }

        override protected Object ReadRow(SqlDataReader reader)
        {
            Order retval = new Order();
            foreach (Order.Fields orderField in Enum.GetValues(typeof(Order.Fields)))
            {
                switch (orderField)
                {
                    case Order.Fields.OrderElement:
                        retval[orderField] = ReadOrderElemnt(reader);
                        break;
                    default:
                        retval[orderField] = reader[orderField.ToString()];
                        break;
                }
            }
            return retval;
        }
       
        abstract protected OrderElement ReadOrderElemnt(SqlDataReader reader);

        //override internal Boolean Insert(Object newObj)
        //{
        //Order order = (Order)newObj;
        //Boolean retval = false;
        //if (order == null)
        //    return retval;


        //string queryString = @"Insert into " + mTableName + " ";
        //string cols = "(", values = "Values(";
        //List<SqlParameter> paramsList = new List<SqlParameter>();

        //int i = 0;
        //string paramName;
        //Order.Fields colName;
        //SqlParameter param;
        //foreach (Order.Fields orderField in Enum.GetValues(typeof(Order.Fields)))
        //{

        //    colName = orderField;
        //    paramName = @"@" + colName + "Param";

        //    if (order[colName] == null || colName == Order.Fields.ID)
        //        continue;

        //    if (i++ > 0)
        //    {
        //        cols += ", ";
        //        values += ", ";
        //    }

        //    switch (colName)
        //    {
        //        case Order.Fields.OrderElement:

        //                OrderElement orderElement = (OrderElement)order[colName];
        //                int j = 0;
        //                foreach (KeyValuePair<Enum, SqlDbType> pair in mTableFields)
        //                {


        //                    Enum elementField = pair.Key;
        //                    if (elementField.ToString() == Order.Fields.ID.ToString() ||
        //                        elementField.ToString() == Order.Fields.ClientID.ToString() ||
        //                        elementField.ToString() == Order.Fields.ExaminationID.ToString() ||
        //                        elementField.ToString() == Order.Fields.OrderDate.ToString() ||
        //                        elementField.ToString() == Order.Fields.Price.ToString())
        //                    {
        //                        continue;
        //                    }
        //                    else
        //                    {
        //                        if (j++ > 0)
        //                        {
        //                            cols += ", ";
        //                            values += ", ";
        //                        }
        //                        paramName = @"@" + elementField + "Param";

        //                        cols += elementField;
        //                        values += paramName;

        //                        param = new SqlParameter(paramName, pair.Value);
        //                        param.Value = (orderElement[elementField]);
        //                        paramsList.Add(param);
        //                    }


        //                }
        //                break;

        //        default:
        //            cols += colName;
        //            values += paramName;
        //            param = new SqlParameter(paramName, mTableFields[colName]);
        //            param.Value = order[colName];
        //            paramsList.Add(param);
        //            break;
        //    }
        //}
        //cols += ")";
        //values += ")";
        //queryString += cols + " " + values + @"; Select @@IDENTITY";

        //SqlCommand command = DBConnection.getCommand();
        //command.CommandText = queryString;
        //foreach (SqlParameter parameter in paramsList)
        //{
        //    command.Parameters.Add(parameter);
        //}

        //Object dbResult = command.ExecuteScalar();
        //if (dbResult == null)
        //    return retval;

        //retval = true;
        //order[Order.Fields.ID] = dbResult;


        //return retval;
        //	}
        // override internal Boolean Update(Object newObj)
        //{
        //    Order order = (Order)newObj;
        //    Boolean retval = false;

        //    if (order == null)
        //        return retval;


        //    string queryString = @"Update  " + mTableName + " ";
        //    string updateString = "";
        //    List<SqlParameter> paramsList = new List<SqlParameter>();

        //    int i = 0;
        //    string paramName;
        //    Order.Fields colName;
        //    SqlParameter param;
        //    foreach (Order.Fields orderField in Enum.GetValues(typeof(Order.Fields)))
        //    {
        //        colName = orderField;
        //        paramName = @"@" + colName + "Param";

        //        if (order[colName] == null || colName == Order.Fields.ID)
        //            continue;

        //        if (i == 0)
        //        {
        //            updateString += "Set ";
        //        }
        //        if (i++ > 0)
        //        {
        //            updateString += ", ";
        //        }


        //        switch (colName)
        //        {
        //            case Order.Fields.OrderElement:

        //                OrderElement orderElement = (OrderElement)order[colName];
        //                int j = 0;
        //                foreach (KeyValuePair<Enum, SqlDbType> pair in mTableFields)
        //                {


        //                    Enum elementField = pair.Key;
        //                    if (elementField.ToString() == Order.Fields.ID.ToString() ||
        //                        elementField.ToString() == Order.Fields.ClientID.ToString() ||
        //                        elementField.ToString() == Order.Fields.ExaminationID.ToString() ||
        //                        elementField.ToString() == Order.Fields.OrderDate.ToString() ||
        //                        elementField.ToString() == Order.Fields.Price.ToString())
        //                    {
        //                        continue;
        //                    }
        //                    else
        //                    {
        //                        if (j++ > 0)
        //                        {
        //                            updateString += ", ";
        //                        }
        //                        paramName = @"@" + elementField + "Param";
        //                        updateString += elementField + " = " + paramName;

        //                        param = new SqlParameter(paramName, pair.Value);
        //                        param.Value = orderElement[elementField];
        //                        paramsList.Add(param);

        //                    }
        //                }
        //                break;

        //            default:
        //                updateString += colName + " = " + paramName;
        //                param = new SqlParameter(paramName, mTableFields[colName]);
        //                param.Value = order[colName];
        //                paramsList.Add(param);
        //                break;
        //        }

        //    }

        //    queryString += updateString + "  where ID =@IDParam";
        //    param = new SqlParameter("@IDParam", mTableFields[Order.Fields.ID]);
        //    param.Value = order[Order.Fields.ID];
        //    paramsList.Add(param);

        //    SqlCommand command = DBConnection.getCommand();
        //    command.CommandText = queryString;
        //    foreach (SqlParameter parameter in paramsList)
        //    {
        //        command.Parameters.Add(parameter);
        //    }

        //    command.ExecuteNonQuery();
        //    return true;
        //}
        //internal Order Retrive(int id)
        //{

        //    Order retval = new Order();
        //    string queryString = @"Select * From " + mTableName + " Where ID =@IDParam ";

        //    SqlParameter param = new SqlParameter("@IDParam", mTableFields[Order.Fields.ID]);
        //    param.Value = id;

        //    SqlCommand command = DBConnection.getCommand();
        //    command.CommandText = queryString;
        //    command.Parameters.Add(param);

        //    SqlDataReader reader = command.ExecuteReader();
        //    while (reader.Read())
        //    {
              
        //    }
        //    reader.Close();
        //    return retval;
        //}
    }


}
